package com.scse.bms.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.scse.bms.domain.MenuItem;
import com.scse.bms.domain.ThemeVO;
import com.scse.bms.entity.ThemeDO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @Title: ThemeMapper
 * @Author: 吴赠禹
 * @Date: 2021/2/19 23:37
 * @Version: 1.0
 * @Description: 使用mybatis-plus，BaseMapper包含了基础的增删改查方法，无需编写mapper.xml文件，即可获得基础CRUD功能
 */

@Repository
@Mapper
public interface ThemeMapper extends BaseMapper<ThemeVO> {

    /**
     * 按书的数量进行归类排序
     * @return
     */
    @Select({"SELECT a.num, b.id, b.name from" +
            "(SELECT count(id) AS num, theme_id id FROM t_book GROUP BY theme_id ORDER BY num DESC) a" +
            " RIGHT JOIN t_theme b ON a.id = b.id order by a.num desc"})
    List<MenuItem> selectTheme();


    /**
     * 2020-3-2 修改后的主题列表查询结果（四张表连接查询，增加每种主题图书数量、主题访问次数、主题图书下载次数）
     * @param page
     * @return
     */
    @Select("select a.id, a.name, b.bookNum, c.visited_count, d.download_count FROM " +
            "t_theme a left join   " +
            "(select theme_id, count(theme_id) as bookNum from t_book group by theme_id) b " +
            "on a.id = b.theme_id " +
            "left join " +
            "(select theme_id, count(theme_id) as visited_count from t_history group by theme_id) c " +
            "on a.id = c.theme_id " +
            "left join " +
            "(select theme_id, count(theme_id) as download_count from t_download group by theme_id) d " +
            "on a.id = d.theme_id")
    IPage<ThemeVO> selectThemePage(Page<?> page);


    /**
     * 根据主题ID查询图书数量（可能为空，所以用Integer）
     */
    @Select("select b.bookNum FROM " +
            "t_theme a inner join   " +
            "(select theme_id, count(theme_id) as bookNum from t_book group by theme_id) b " +
            "on a.id = b.theme_id and a.id = #{themeId}")
    Integer selectBookNumByThemeId(int themeId);


    /**
     * 查询所有主题拥有图书数量、访问次数、下载次数
     * null不影响echarts渲染
     * @return
     */
    @Select("select a.id, a.name, b.bookNum, c.visited_count, d.download_count FROM " +
            "t_theme a left join   " +
            "(select theme_id, count(theme_id) as bookNum from t_book group by theme_id) b " +
            "on a.id = b.theme_id " +
            "left join " +
            "(select theme_id, count(theme_id) as visited_count from t_history group by theme_id) c " +
            "on a.id = c.theme_id " +
            "left join " +
            "(select theme_id, count(theme_id) as download_count from t_download group by theme_id) d " +
            "on a.id = d.theme_id")
    List<ThemeVO> selectThemeList();


    /**
     * 查询所有主题的日均、月均、年均访问量、向上取整（使用左连接、Left截取函数，集合函数）
     */
    @Select("select t.id, t.name, ifnull(dayAvg,0) dayAvg, ifnull(monAvg,0) monAvg, ifnull(yearAvg,0) yearAvg from  " +
            "t_theme t left join  " +
            "( " +
            "select theme_id, ceil(avg(day_count)) as dayAvg from  " +
            "(select theme_id, count(*) as day_count, left(time,10) as date " +
            "from t_history  group by left(time, 10), theme_id ) a group by theme_id " +
            ") tday on t.id = tday.theme_id " +
            "left JOIN " +
            "( " +
            "select theme_id, ceil(avg(month_count)) as monAvg from  " +
            "(select theme_id, count(*) as month_count, left(time,7) as date " +
            "from t_history  group by left(time, 7), theme_id ) a group by theme_id " +
            ") tmonth on t.id = tmonth.theme_id " +
            "left JOIN " +
            "( " +
            "select theme_id, ceil(avg(month_count)) as yearAvg from  " +
            "(select theme_id, count(*) as month_count, left(time,4) as date " +
            "from t_history  group by left(time, 4), theme_id ) a group by theme_id " +
            ") tyear on t.id = tyear.theme_id")
    List<ThemeVO> selectThemeVisitedListGroupByDayAndMonthAndYearWithAvg();


    /**
     * 查询所有主题的日均、月均、年均下载量（null值会影响）
     */
    @Select("select t.id, t.name, ifnull(dayAvg,0) dayAvg, ifnull(monAvg,0) monAvg, ifnull(yearAvg,0) yearAvg from  " +
            "t_theme t left join  " +
            "( " +
            "select theme_id, ceil(avg(day_count)) as dayAvg from  " +
            "(select theme_id, count(*) as day_count, left(time,10) as date " +
            "from t_download  group by left(time, 10), theme_id ) a group by theme_id " +
            ") tday on t.id = tday.theme_id " +
            "left JOIN " +
            "( " +
            "select theme_id, ceil(avg(month_count)) as monAvg from  " +
            "(select theme_id, count(*) as month_count, left(time,7) as date " +
            "from t_download  group by left(time, 7), theme_id ) a group by theme_id " +
            ") tmonth on t.id = tmonth.theme_id " +
            "left JOIN " +
            "( " +
            "select theme_id, ceil(avg(month_count)) as yearAvg from  " +
            "(select theme_id, count(*) as month_count, left(time,4) as date " +
            "from t_download  group by left(time, 4), theme_id ) a group by theme_id " +
            ") tyear on t.id = tyear.theme_id")
    List<ThemeVO> selectThemeDownloadListGroupByDayAndMonthAndYearWithAvg();


    /**
     * 每种主题推荐的图书数量
     */
    @Select("select count(theme_id) as value, name from  " +
            "( " +
            "select book_id, theme_id from t_recommend recommend " +
            "left join t_book book on recommend.book_id = book.id " +
            ") a left join t_theme b on a.theme_id = b.id " +
            "group by theme_id ")
    List<ThemeVO> selectThemeRecommendBookNum();

}
